package com.senney.practice.spring.jdbc;

import com.senney.practice.spring.bean.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.util.List;

@Component
public class JdbcTempCRUD {
    @Autowired
    private JdbcTemplate jdbcTemplate=null;
    public int insertEmployee(Employee employee) {
        String sql = "insert into tab_employee(last_name,email,gender) values(?,?,?)";
        return jdbcTemplate.update(sql, employee.getLastName(), employee.getEmail(), employee.getGender());
    }

    public int updateEmployee(Employee employee) {
        String sql = "update tab_employee set last_name = ?, email = ?, gender = ? where id = ?";
        return jdbcTemplate.update(sql, employee.getLastName(), employee.getEmail(), employee.getGender(), employee.getId());
    }

    public int deleteEmployee(Long id) {
        String sql = "delete from tab_employee where id = ?";
        return jdbcTemplate.update(sql, id);
    }

    public List<Employee> findEmployee(String lastName) {
        String sql = "select id,last_name,email,gender from tab_employee where last_name like concat('%',?,'%')";
        Object[] params = {lastName};
        List<Employee> employeeList = jdbcTemplate.query(sql, params, (rs, i) -> {
            Employee emp = new Employee();
            emp.setId(rs.getInt("id"));
            emp.setLastName(rs.getString("last_name"));
            emp.setEmail(rs.getString("email"));
            emp.setGender(rs.getString("gender"));
            return emp;
        });
        return employeeList;
    }

    public Employee findEmployeeById(Long id) {
        String sql = "select id,last_name,gender,email from tab_employee where id = ?";
        Employee employee = jdbcTemplate.queryForObject(sql, new Object[]{id}, (resultSet, i) -> {
            Employee emp = new Employee();
            emp.setId(resultSet.getInt("id"));
            emp.setLastName(resultSet.getString("last_name"));
            emp.setEmail(resultSet.getString("email"));
            emp.setGender(resultSet.getString("gender"));
            return emp;
        });
        return employee;
    }
}
